/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Dosen;
import Entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import library.Koneksi;


/**
 *
 * @author mr2
 */
public class DosenModel {
    
        
    Connection koneksi;
    Dosen dosen;
    User user;
    
    public DosenModel(){
        koneksi = Koneksi.getKoneksi();
    }
	
	public List<Dosen> DaftarDosen(){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Dosen> listDosen = new ArrayList<Dosen>();
        Connection conn =  Koneksi.getKoneksi();
        try{
            String sql = "SELECT dosen.id_user, user.nama_user "
                    + "FROM dosen, user "
                    + "where user.id_user = dosen.id_user and status_dosen = 'tetap' "
                    + "order by user.nama_user asc";
            
            prepare = conn.prepareStatement(sql);
            
            result = prepare.executeQuery();
            
            while(result.next()){
                Dosen dosen = new Dosen();
                User user = new User();
                user.setIdUser(result.getString("ID_USER"));
                user.setNamaUser(result.getString("NAMA_USER"));
                dosen.setUser(user);
                
                listDosen.add(dosen);
            }
            return listDosen;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listDosen;            
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
    public List<Dosen> ListPembimbing1(){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Dosen> listDosen = new ArrayList<Dosen>();
        
        try{
            String sql = "SELECT dosen.id_user, user.nama_user, status_pembimbing1 = 1 "
                    + "FROM dosen, user "
                    + "where user.id_user = dosen.id_user and status_pembimbing1 = 1 ";
            
            prepare = koneksi.prepareStatement(sql);
            
            result = prepare.executeQuery();
            
            while(result.next()){
                Dosen dosen = new Dosen();
                User user = new User();
                user.setIdUser(result.getString("ID_USER"));
                user.setNamaUser(result.getString("NAMA_USER"));
                dosen.setUser(user);
                
                listDosen.add(dosen);
            }
            return listDosen;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listDosen;            
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
    public List<Dosen> ListPembimbing(){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Dosen> listDosen = new ArrayList<Dosen>();
        
        try{
            String sql = "SELECT dosen.id_user, user.nama_user, status_pembimbing1 "
                    + "FROM dosen, user "
                    + "where user.id_user = dosen.id_user and status_dosen = 'tetap' ";
            
            prepare = koneksi.prepareStatement(sql);
            
            result = prepare.executeQuery();
            
            while(result.next()){
                Dosen dosen = new Dosen();
                User user = new User();
                user.setIdUser(result.getString("ID_USER"));
                user.setNamaUser(result.getString("NAMA_USER"));
                dosen.setUser(user);
                dosen.setStatusPembimbing1(result.getString("status_pembimbing1"));
                
                listDosen.add(dosen);
            }
            return listDosen;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listDosen;            
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
//    public List<Dosen> ListPembimbing2(){
//        
//        PreparedStatement prepare = null;
//        ResultSet result = null;
//        List<Dosen> listDosen = new ArrayList<Dosen>();
//        
//        try{
//            String sql = "SELECT dosen.id_user, user.nama_user "
//                    + "FROM dosen, user "
//                    + "where user.id_user = dosen.id_user  and status_dosen = 'tetap' "
//                    + "order by nama_user asc";
//            
//            prepare = koneksi.prepareStatement(sql);
//            
//            result = prepare.executeQuery();
//            
//            while(result.next()){
//                Dosen dosen = new Dosen();
//                User user = new User();
//                user.setIdUser(result.getString("ID_USER"));
//                user.setNamaUser(result.getString("NAMA_USER"));
//                dosen.setUser(user);
//                
//                listDosen.add(dosen);
//            }
//            return listDosen;
//            
//        }catch(SQLException ex){
//            System.out.print(ex.getMessage());
//            return listDosen;            
//        }finally{
//            if(prepare != null){
//                try{
//                    prepare.close();
//                }catch(SQLException ex){
//                    
//                }
//            }
//            if(result != null){
//                try{
//                    result.close();
//                }catch(SQLException ex){
//                    
//                }
//            }
//        }
//    }
    
    public List<Dosen> ListPenguji1(String pembimbing1){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Dosen> listDosen = new ArrayList<Dosen>();
        
        try{
            String sql = "SELECT dosen.id_user, user.nama_user "
                    + "FROM dosen, user "
                    + "where user.id_user = dosen.id_user  and status_dosen = 'tetap' and dosen.id_user <> ? and status_pembimbing1 = 1 "
                    + "order by nama_user asc";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setString(1,pembimbing1);
            
            result = prepare.executeQuery();
            
            while(result.next()){
                Dosen dosen = new Dosen();
                User user = new User();
                user.setIdUser(result.getString("ID_USER"));
                user.setNamaUser(result.getString("NAMA_USER"));
                dosen.setUser(user);
                
                listDosen.add(dosen);
            }
            return listDosen;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listDosen;            
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
    public List<Dosen> ListPenguji2(String pembimbing2){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<Dosen> listDosen = new ArrayList<Dosen>();
        
        try{
            String sql = "SELECT dosen.id_user, user.nama_user "
                    + "FROM dosen, user "
                    + "where user.id_user = dosen.id_user and status_dosen = 'tetap' and dosen.id_user <> ? "
                    + "order by nama_user asc";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setString(1,pembimbing2);
            
            result = prepare.executeQuery();
            
            while(result.next()){
                Dosen dosen = new Dosen();
                User user = new User();
                user.setIdUser(result.getString("ID_USER"));
                user.setNamaUser(result.getString("NAMA_USER"));
                dosen.setUser(user);
                
                listDosen.add(dosen);
            }
            return listDosen;
            
        }catch(SQLException ex){
            System.out.print(ex.getMessage());
            return listDosen;            
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        }
    }
    
    public void setPembimbing1(Dosen dosen){
        PreparedStatement prepare = null;
            try{            
            
            String sql = "update dosen set status_pembimbing1 = ? where id_user = ? ";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, dosen.getStatusPembimbing1());
            prepare.setString(2, dosen.getUser().getIdUser());
            
            prepare.executeUpdate();
            
            }catch(SQLException ex){
                System.out.println(ex.getMessage());
            }finally{
                if(prepare != null){
                    try{
                        prepare.close();
                    }catch(SQLException ex){

                    }
                }
            }
    }
}
